package cn.b.sky.common.file;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <strong>Title : ExcelUtil.java<br>
 * </strong> <strong>Package : cn.b.sky..utils.file<br>
 * </strong> <strong>Description : </strong>@类注释说明写在此处@<br>
 * <strong>Create on : 2015年10月12日 下午4:10:00<br>
 * </strong>
 * <p>
 * 
 * @author zhanghongjiang<br>
 * @version <strong>v1.0.0</strong><br>
 *          <br>
 *          <strong>修改历史:</strong><br>
 *          修改人 | 修改日期 | 修改描述<br>
 *          -------------------------------------------<br>
 *          <br>
 *          <br>
 */
@Slf4j
public class ExcelUtil {

  public static final String COLUMN_NAME = "columnName";

  public static final String COLUMN_TEXT = "columnText";

  public static final String COLUMN_WIDTH = "columnWidth";

  public static final int COLUMN_WIDTH_FACTOR = 25;

  public static final String GRID = "grid";

  public static final String IMAGE = "image";

  private static final int MAX_ROW_NUM = 5000;

  private ExcelUtil() {
    // Hide the constructor
  }

  /**
   * 
   * <strong>Title : exportToExcel<br>
   * </strong> <strong>Description : </strong>导出报表工具<br>
   * <strong>Create on : 2015年10月13日 上午11:00:49<br>
   * </strong>
   * <p>
   * 
   * @param response
   * @param title
   *            excel 标题
   * @param colsMap
   *            列名与列字段映射
   * @param dataList
   *            数据列表
   * @throws IOException
   *             void
   * @throws @author
   *             zhanghongjiang<br>
   * @version <strong>v1.0.0</strong><br>
   *          <br>
   *          <strong>修改历史:</strong><br>
   *          修改人 | 修改日期 | 修改描述<br>
   *          -------------------------------------------<br>
   *          <br>
   *          <br>
   */
  public static void exportToExcel(HttpServletResponse response, String title, Map<String, String> colsMap,
      List<?> dataList) throws IOException {

    OutputStream out;
    out = response.getOutputStream();
    try {
      response.setHeader("Content-disposition", "attachment; filename="
          + new String(title.getBytes("gb2312"), "iso8859-1") + ".xls");// 设定输出文件头
      response.setContentType("application/msexcel");// 定义输出类型

      Workbook wb = new HSSFWorkbook();
      Sheet s = null;
      Row row = null;
      CellStyle cs = wb.createCellStyle();
      cs.setAlignment((short) 0x2);
      int rownum = 0;
      String[] attr = null;
      
      // 表内容
      for (Object model : dataList) {
        //5000行换一个sheet页
        if (rownum % MAX_ROW_NUM == 0) {
          s = createSheet(wb, title);
          attr = createSheetHeader(s, colsMap);
          rownum = 0;
        }
        row = s.createRow(++rownum);
        ArrayList<Field> fields = getFields(model);
        Field field = null;

        for (int colIndex = 0; colIndex < attr.length; colIndex++) {
          for (int i = 0; i < fields.size(); i++) {
            field = fields.get(i);
            field.setAccessible(true);
            if (attr[colIndex].equalsIgnoreCase(field.getName())) {
              Cell cell = row.createCell(colIndex);
              PropertyDescriptor pd = new PropertyDescriptor(field.getName(),  
                  model.getClass());  
              Method getMethod = pd.getReadMethod();//获得get方法  
              Object value = getMethod.invoke(model);//执行get方法返回一个Object 
              setCellValue(cell, value);
              break;
            }
          }
        }
      }
      wb.write(out);
    } catch (Exception e) {
      log.error("{}", e);
    } finally {
      if (out != null) {
        out.close();
      }
    }
  }

  /**
   * 
   * <strong>Title : createSheet<br></strong>
   * <strong>Description : </strong>创建sheet<br> 
   * <strong>Create on : 2016年7月15日 上午11:00:46<br></strong>
   * <p>
   * @param workbook
   * @param title
   * @return
   * Sheet
   * @throws 
   * @author Yantao.Lu<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  private static Sheet createSheet(Workbook workbook, String title) {
    if (null != workbook) {
      int index = workbook.getNumberOfSheets();
      if (index == 0) {
        return workbook.createSheet(title);
      } else if (index == 1) {
        workbook.setSheetName(0, title + 1);
      }
      return workbook.createSheet(title + (index + 1));
    }
    return null;
  }

  /**
   * 
   * <strong>Title : createSheetHeader<br></strong>
   * <strong>Description : </strong>写表头<br> 
   * <strong>Create on : 2016年7月15日 上午11:16:16<br></strong>
   * <p>
   * @param sheet
   * @param colsMap
   * @return
   * String[]
   * @throws 
   * @author Yantao.Lu<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  private static String[] createSheetHeader(Sheet sheet, Map<String, String> colsMap) {
    int colIndex = 0;
    String[] attr = new String[colsMap.size()];
    Row row = sheet.createRow(0);
    for (String key : colsMap.keySet()) {
      attr[colIndex] = key;
      String colHeader = colsMap.get(key); // 国际化考虑
      Cell cell = row.createCell(colIndex++);
      cell.setCellValue(colHeader);
    }
    for (colIndex = 0; colIndex < attr.length; colIndex++) {
      sheet.setColumnWidth(colIndex, attr[colIndex].length() * 256);
    }
    return attr;
  }

  /**
   * 
   * <strong>Title : exportToExcel<br>
   * </strong> <strong>Description : </strong>根据页面表头导出<br>
   * <strong>Create on : 2016年3月16日 下午1:52:30<br>
   * </strong>
   * <p>
   * 
   * @param response
   * @param title
   * @param columnMaps
   * @param dataList
   * @throws IOException
   *             void
   * @throws @author
   *             zhanghongjiang<br>
   * @version <strong>v1.0.0</strong><br>
   *          <br>
   *          <strong>修改历史:</strong><br>
   *          修改人 | 修改日期 | 修改描述<br>
   *          -------------------------------------------<br>
   *          <br>
   *          <br>
   */
  public static <T> void exportToExcel(HttpServletResponse response, String title, List<Map> columnMaps,
      List<Map<String, Object>> dataList) throws IOException {

    OutputStream out;
    out = response.getOutputStream();
    try {
      int rownum = 0;

      response.setHeader("Content-disposition", "attachment; filename="
          + new String(title.getBytes("gb2312"), "iso8859-1") + ".xls");// 设定输出文件头
      response.setContentType("application/msexcel");// 定义输出类型

      Workbook wb = new HSSFWorkbook();
      Sheet s = wb.createSheet();
      Row row = null;
      CellStyle cs = wb.createCellStyle();
      cs.setAlignment((short) 0x2);

      String[] fields = new String[columnMaps.size()];

      int colIndex = 0;
      row = s.createRow(rownum);
      // 表头
      for (Map<String, Object> map : columnMaps) {
        Cell cell = row.createCell(colIndex);
        cell.setCellValue(map.get(COLUMN_TEXT).toString());
        String ss = map.get(COLUMN_WIDTH).toString();
        s.setColumnWidth(colIndex, COLUMN_WIDTH_FACTOR * Float.valueOf(ss).intValue());
        fields[colIndex] = map.get(COLUMN_NAME).toString();
        colIndex++;
      }

      // 表内容
      for (Map<String, Object> model : dataList) {
        row = s.createRow(++rownum);

        for (colIndex = 0; colIndex < fields.length; colIndex++) {
          Cell cell = row.createCell(colIndex);
          Object value = model.get(fields[colIndex].toUpperCase());
          setCellValue(cell, value);
        }
      }

      wb.setSheetName(0, title);
      wb.write(out);

    } catch (Exception e) {
      log.error("{}", e);
    } finally {
      if (out != null) {
        out.close();
      }
    }
  }

  /**
   * <strong>Title : getFields<br>
   * </strong> <strong>Description : </strong>获取对象属性列表<br>
   * <strong>Create on : 2015年10月19日 上午9:20:33<br>
   * </strong>
   * <p>
   * 
   * @param model
   * @return ArrayList<Field>
   * @throws @author
   *             zhanghongjiang<br>
   * @version <strong>v1.0.0</strong><br>
   *          <br>
   *          <strong>修改历史:</strong><br>
   *          修改人 | 修改日期 | 修改描述<br>
   *          -------------------------------------------<br>
   *          <br>
   *          <br>
   */
  private static <T> ArrayList<Field> getFields(T model) {
    ArrayList<Field> all = new ArrayList<Field>(20);
    Class<?> clz = model.getClass();
    while (clz != null) {
      Field[] fields = clz.getDeclaredFields();
      all.addAll(Arrays.asList(fields));
      clz = clz.getSuperclass();
    }
    return all;
  }

  public static void setCellValue(Cell cell, Object value) {
    if (value == null) {
      cell.setCellValue("");
    } else if (value instanceof String) {
      cell.setCellValue(value.toString());
    } else if (value instanceof Date) {
      cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) value));
    } else {
      cell.setCellValue(String.valueOf(value));
    }
  }

  /**
   * 
   * <strong>Title : parseColumnsStr2Map<br></strong>
   * <strong>Description : </strong>将有序的列信息转为有序的map<br> 
   * <strong>Create on : 2016年5月24日 上午11:12:30<br></strong>
   * <p>
   * @param colsMap
   * @return
   * LinkedHashMap<String,String>
   * @throws 
   * @author Yantao.Lu<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  @SuppressWarnings("unchecked")
  public static LinkedHashMap<String, String> parseColumnsStr2Map(String colsMap) {
    LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
    if (!StringUtils.isBlank(colsMap)) {
      map = JSON.parseObject(colsMap, LinkedHashMap.class);
    }
    return map;
  }
  
  /**
   * 
   * <strong>Title : parseTableMapStr2TitleMap<br></strong>
   * <strong>Description : </strong>获取单个table的表头map<br> 
   * <strong>Create on : 2016-8-8 下午6:04:47<br></strong>
   * <p>
   * @param tableMap
   * @param tableId
   * @return
   * Map<String,String>
   * @throws 
   * @author lidapeng<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  @SuppressWarnings("unchecked")
  public static Map<String, String> parseTableMapStr2TitleMap(String tableMap, String tableId) {
    if(StringUtils.isBlank(tableMap)){
      return null;
    }
    Map<String, String> map = new LinkedHashMap<String, String>();
    Map<String, Object> object = JSONObject.parseObject(tableMap);
    List<Map<String, String>> maps = (List<Map<String, String>>) object.get(tableId);
    for(Map<String, String> table : maps){
      map.put(table.get(COLUMN_NAME), table.get(COLUMN_TEXT));
    }
    return map;
  }
}
